using System.Collections.Generic;
using System.Data;
using KenEgozi.Com.Domain.Interfaces.Services;
using KenEgozi.Com.Domain.Projections;
using Rhino.Commons;

namespace KenEgozi.Com.Domain.Services
{
	public class BlogProjectionsService : IBlogProjectionsService
	{
		public Stats GetStats()
		{
			Stats stats;

			IDbConnection con = UnitOfWork.CurrentSession.Connection;
			
			using(IDbCommand cmd = con.CreateCommand())
			{
				cmd.CommandText = "SELECT COUNT(Id) FROM Posts";
				int postsCount = (int)cmd.ExecuteScalar();
				cmd.CommandText = "SELECT COUNT(Id) FROM Comments WHERE Status=2";
				int commentsCount = (int)cmd.ExecuteScalar();

				stats = new Stats(postsCount, commentsCount);
			}

			return stats;

		}

		public ICollection<ArchiveEntry> GetArchiveEntries()
		{
			string hql = @"
                select new ArchiveEntry(year(p.Lifecycle.CreationDate), month(p.Lifecycle.CreationDate), count(p.Id)) 
                from Post p
                order by year(p.Lifecycle.CreationDate) desc, month(p.Lifecycle.CreationDate) desc
                group by year(p.Lifecycle.CreationDate), month(p.Lifecycle.CreationDate)";

			return UnitOfWork.CurrentSession
				.CreateQuery(hql)
				.List<ArchiveEntry>();
		}
		public ICollection<TagCloudEntry> GetTagsData()
		{
			string hql = @"
                select new TagCloudEntry(t.Name, t.UrlFriendlyName, count(p)) 
                from 
					Tag t inner join
					t.Posts p
                order by t.UrlFriendlyName
                group by t.Name, t.UrlFriendlyName";
			return UnitOfWork.CurrentSession
				.CreateQuery(hql)
				.List<TagCloudEntry>();
		}

	}
}